USE [dbproc]
GO

/****** Object:  StoredProcedure [dbo].[spx_dashboard]    Script Date: 03/28/2012 13:10:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 2012-05-17 EM
alter procedure [dbo].[spx_dashboard]
        @sid uniqueidentifier,
        @xml xml output
as
begin
set nocount on;

exec sms_p.dbo.prints 'DAB 000','spx_dashboard:', @sid,32
        
declare @uid int set @uid = null;
declare @now datetime set @now = getdate();

exec dbproc.dbo.sp_get_user_by_session @sid, @uid output;
        
declare @prod table(id int, title nvarchar(250));
declare @Total int, @OK int, @Errors int, @Waited int;
        -- user found
if @uid is not null
	begin
    insert into @prod
           select top 5 id, title from dbdata.dbo.products order by newid();

	select @Total=count(s.[Id]) 
			FROM 
			[sms].[dbo].[SMS] s
			left join [sms].[dbo].[SMSPay] sp on sp.sms_id=s.id  
			left  join meps.dbo.AutomatLog a on a.sms_id=s.id
			join dbdata.dbo.phones p on ((p.phone=s.[Address]) or (p.phone=sp.[Phone]) or (p.phone=sp.[MainPhone]) or (p.phone=sp.[TagPhone]  )) 
			where  (p.uid=@uid)and 
			isnull(CmdCode,'NULL') not in ('?','PIN','DBC','ba')
			and substring(s.Message,1,3)  not in ('PIN','DBC')
			and substring(s.Message,1,2)  not in ('ba','? ')
			
			
                           
	select @OK=count(s.[Id])
			FROM 
			[sms].[dbo].[SMS] s
			left join [sms].[dbo].[SMSPay] sp on sp.sms_id=s.id  
			left  join meps.dbo.AutomatLog a on a.sms_id=s.id
			join dbdata.dbo.phones p on ((p.phone=s.[Address]) or (p.phone=sp.[Phone]) or (p.phone=sp.[MainPhone]) or (p.phone=sp.[TagPhone]  )) 
			where  (p.uid=@uid)and 
			isnull(CmdCode,'NULL') not in ('?','PIN','DBC','ba')
			and substring(s.Message,1,3)  not in ('PIN','DBC')
			and substring(s.Message,1,2)  not in ('ba','? ')
			and a.Res=0
			
			
    set @Errors=0                       
	select @Errors= count(s.[Id])
			FROM 
			[sms].[dbo].[SMS] s
			left join [sms].[dbo].[SMSPay] sp on sp.sms_id=s.id  
			left  join meps.dbo.AutomatLog a on a.sms_id=s.id
			join dbdata.dbo.phones p on ((p.phone=s.[Address]) or (p.phone=sp.[Phone]) or (p.phone=sp.[MainPhone]) or (p.phone=sp.[TagPhone]  )) 
			where  (p.uid=@uid)and 
			isnull(CmdCode,'NULL') not in ('?','PIN','DBC','ba')
			and substring(s.Message,1,3)  not in ('PIN','DBC')
			and substring(s.Message,1,2)  not in ('ba','? ')
			and a.Res not in (0,-1,-2)

	select @Errors=@Errors+ count(s.[Id])
			FROM 
			[sms].[dbo].[SMS] s
			left join [sms].[dbo].[SMSPay] sp on sp.sms_id=s.id  
			left  join meps.dbo.AutomatLog a on a.sms_id=s.id
			join dbdata.dbo.phones p on ((p.phone=s.[Address]) or (p.phone=sp.[Phone]) or (p.phone=sp.[MainPhone]) or (p.phone=sp.[TagPhone]  )) 
			where  (p.uid=@uid)and 
			isnull(CmdCode,'NULL') not in ('?','PIN','DBC','ba')
			and substring(s.Message,1,3)  not in ('PIN','DBC')
			and substring(s.Message,1,2)  not in ('ba','? ')
			and a.sms_id is null

			
                           
	select @Waited= count(s.[Id]) 
			FROM 
			[sms].[dbo].[SMS] s
			left join [sms].[dbo].[SMSPay] sp on sp.sms_id=s.id  
			left  join meps.dbo.AutomatLog a on a.sms_id=s.id
			join dbdata.dbo.phones p on ((p.phone=s.[Address]) or (p.phone=sp.[Phone]) or (p.phone=sp.[MainPhone]) or (p.phone=sp.[TagPhone]  )) 
			where  (p.uid=@uid)and 
			isnull(CmdCode,'NULL') not in ('?','PIN','DBC','ba')
			and substring(s.Message,1,3)  not in ('PIN','DBC')
			and substring(s.Message,1,2)  not in ('ba','? ')
			
			and a.Res  in (-1,-2)

                        
    set @xml = 
    (
     
    select 
		(
			select s.[Id] as id
			--,dbo.fn_dt_format(s.GetTime) as GetTime       
			,s.GetTime as GetTime       
			,isnull(sp.[MainPhone],s.Address) as MainPhone
			,isnull(sp.[TagPhone],s.Address) as TagPhone
			,isnull(sp.[CmdCode], '') as CmdCode
			,isnull(sp.[FullSum2],0) as FullSum2
			,isnull(sp.[TransferSum2],0) as TransferSum2
			,isnull(cast(a.Res as varchar),'') as res
			,isnull(sp.[ServiceResultText],s.Message) + ' '+isnull(isnull(a.Result, sp.[ServiceResultText]),s.Comment)  as ServiceResultText
--			,dbo.fn_dt_format(isnull(isnull(a.DateTimePro,sp.ResultServiceTime),s.ProTime)) as DateTimePro                      
			,isnull(isnull(a.DateTimePro,sp.ResultServiceTime),s.ProTime) as DateTimePro                      
                        
			FROM 
			[sms].[dbo].[SMS] s
			left join [sms].[dbo].[SMSPay] sp on sp.sms_id=s.id 
			left  join meps.dbo.AutomatLog a on a.sms_id=s.id
			join dbdata.dbo.phones p on ((p.phone=s.[Address]) or (p.phone=sp.[Phone]) or (p.phone=sp.[MainPhone]) or (p.phone=sp.[TagPhone]  )) 
			where  (p.uid=@uid)and 
			isnull(CmdCode,'NULL') not in ('?','PIN','DBC','ba')
			and substring(s.Message,1,3)  not in ('PIN','DBC')
			and substring(s.Message,1,2)  not in ('ba','? ')
			order by s.Id desc
                           
			for xml raw('operation'), type
		) as operations,
  
                             
        (
			select 
            a.id, 
            c.title, 
            cast(a.summary as decimal(20,2)) summary, 
            a.enabled 
            from dbdata.dbo.accounts a, dbdata.dbo.account_categs c 
            where c.id = a.categ_id and a.uid = @uid for xml raw('account'), type
        ) as accounts,
        
        (
			 select @Total as total, @OK as ok, @Errors as errors, @Waited as waited for xml raw('payments'), type
		) as paymentcounts,

       

        (
			select top 3 id, dt, title, info from dbdata.dbo.promos order by newid() for xml raw('promo'), type
		) as promos

        for xml raw('root')
    );
        end else
        -- unknown user 
        begin
                insert into @prod
                        select top 5 id, title from dbdata.dbo.products order by newid();
                set @xml = (
                select 
                        (select id, title from @prod for xml raw('prod'), type) as products,
                        (select top 3 id, dt, title, info from dbdata.dbo.promos order by newid() for xml raw('promo'), type) as promos
                  for xml raw('root')
                );
        end;
end;
go

/*
declare @xml xml
 exec dbproc.[dbo].[spx_dashboard]      '03647D2B-83C6-4B38-96F4-FB5ACE1359B7',
        @xml output
select @xml

declare @xml xml
 exec dbproc.[dbo].[spx_dashboard]      NULL,
        @xml output
select @xml

*/